<?php
if(!defined('BASEPATH')) exit('No direct script access allowed');
/* 
	Excel类
*/
require_once(APPCOMMON.'libraries/PHPExcel.php');

class MyExcel{
	
	public $_excel;
	public $cell_names = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'];
	public $sheet;
	
	public function __construct(){
		$this->_excel = new PHPExcel();
	}
	
	/* 
		写入
		$options 参数说明：
			fields 		= array 字段名称 key => value
			sheet		= int 当前使用的表
			title		= string 表的标题
			freeze		= int 设置从第几行以上浮动
			save_path	= string 保存路径
			type		= string excel版本
			
	*/
	public function write($options, $data = array()){
		if(!$options['fields']){
			return $this->error('字段名称不能为空');
		}
		$fields = $options['fields'];
		$title = $options['title'];
		$type = $options['type'] ? $options['type'] : 'Excel2007';
		
		$cell_len = count($fields);
		$this->_excel = new PHPExcel();
		$this->_excel->setActiveSheetIndex($options['sheet'] ? $options['sheet'] : 0);
		$sheet = $this->_excel->getActiveSheet();
		
		$sheet->getDefaultStyle()->applyFromArray([
			'font' => [
				'name' => '微软雅黑',
				'size' => 10
			],
			'alignment' => [
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
				'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
			]
		]);
		
		// 从第几行开始写入数据
		$start_row = 1;
		
		// 标题
		if($title){
			$sheet->mergeCells('A1:'.$this->cell_names[$cell_len - 1].'1')->setCellValue('A1', $title)->getStyle('A1')->applyFromArray([
				'font' => [
					'bold' => true,
					'size' => 14
				],
				'alignment' => [
					'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
				]
			]);
			$start_row += 1;
		}
		
		// 说明
		/* $note = "说明：\n";
		$cell_name = 'A2:'.$cellNames[$cellLenth - 1].'2';
		$sheet->mergeCells($cell_name)->setCellValue('A2', $note)->getStyle('A2')->applyFromArray([
			'font' => [
				'color' => [
					'argb' => '002377e8'
				]
			],
			'alignment' => [
				'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT
			]
		]);
		$sheet->mergeCells($cell_name)->getStyle('A2')->getAlignment()->setWrapText(true);
		$sheet->getRowDimension('2')->setRowHeight(102); */
		
		// 写入字段名
		$i = 0;
		foreach($fields as $key => $val){
			$col = $this->cell_names[$i];
			$sheet->getStyle($col)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
			$cell = $col.$start_row;
			if(is_array($val)){
				$width = $val['width'];
				$value = $val['name'];
			}else{
				$width = 0;
				$value = $val;
			}
			$sheet->setCellValue($cell, $value)->getStyle($cell)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			if(!$width){
				$width = strlen($value);
			}
			$sheet->getColumnDimension($col)->setWidth($width);
			$i++;
		}
		$start_row += 1;
		
		// 设置开始浮动行
		$freeze = $options['freeze'] > 0 ? $options['freeze'] : $start_row;
		$sheet->freezePane('A'.$freeze);
		
		// 写入数据
		if(is_array($data) && count($data) > 0){
			for($i = 0; $i < count($data); $i++){
				$row = $data[$i];
				$j = 0;
				foreach($fields as $key => $val){
					$col = $this->cell_names[$j];
					$cell = $col.$start_row;
					$value = $row[$key];
					if(is_numeric($value)){
						$sheet->setCellValueExplicit($cell, $value, PHPExcel_Cell_DataType::TYPE_STRING);
					}else{
						$sheet->setCellValue($cell, $value);
					}
					$j++;
				}
				$start_row++;
			}
		}
		$writer = PHPExcel_IOFactory::createWriter($this->_excel, $type);
		
		// 文件名
		$file_name = date('YmdHis');
		if($title){
			$file_name = $title.'_'.$file_name;
		}
		switch($type){
			case 'Excel5':
				$file_type = 'xls';
				break;
			default:
				$file_type = 'xlsx';
		}
		$file_name = $file_name.'.'.$file_type;
		
		// 输出
		if($options['save_path']){
			$writer->save(rtrim($options['save_path'], '/').'/'.$file_name);
		}else{
			header('Content-Type: applicationnd.ms-excel;charset=utf-8');
			header('Content-Disposition: attachment;filename='.urlencode($file_name));
			header('Cache-Control: max-age=0');
			$writer->save('php://output');
		}
	}
	
	// 读取
	public function read($file, $options = array()){
		$file_type = strtolower(substr(strrchr($file, '.'), 1));
		if('xls' == $file_type){
			$reader = PHPExcel_IOFactory::createReader('Excel5');
		}else if('xlsx' == $file_type){
			$reader = new PHPExcel_Reader_Excel2007();
		}else{
			return $this->error('仅支持xls或xlsx文件');
		}
		$PHPExcel = $reader->load($file, $options['char'] ? $options['char'] : 'utf-8');
		
		// 读取第几个個工作表
		$sheet = $PHPExcel->getSheet($options['sheet'] ? $options['sheet'] : 0);
		
		// 取得总行数
		$totalRow = $sheet->getHighestRow(); 
		
		// 取得总列数字母
		$maxCol = $sheet->getHighestColumn(); 
		
		// 总列数字母转换为数字
		$toalCol = PHPExcel_Cell::columnIndexFromString($maxCol);
		$result = array();
		
		// 数据从第几行开始
		$row_start = $options['row_start'] ? $options['row_start'] : 2;
		
		for($i = $row_start; $i <= $totalRow; $i++){
			$row = array();
			for ($j = 0; $j < $toalCol; $j++) {
				$value = $sheet->getCellByColumnAndRow($j, $i)->getValue();
				$value = (string)$value;
				$row[PHPExcel_Cell::stringFromColumnIndex($j)] = trim($value);
			}
			if(implode('', $row) !== ''){
				$result[] = $row;
			}
		}
		if(count($result) < 1){
			return $this->error('数据为空');
		}
		return [
			'error' => 0,
			'data' => $result
		];
	}
	
	private function error($message = '文件错误'){
		return [
			'error' => 1,
			'message' => $message
		];
	}
	
}